package com.animee.tally.db;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.animee.tally.util.FloatUtil;
import java.util.ArrayList;
import java.util.List;
/*
* 负责管理数据库的类
*     主要是对于表当中的内容，进行增删该查
* */
public class DBManager {

    private static SQLiteDatabase db;
    /* 初始化数据库对象*/
    public static void initDB(Context context){
//        1.获取帮助类对象
        DBOpenHelper helper = new DBOpenHelper(context);
//        2.获得数据库对象
        db = helper.getWritableDatabase();
    }
    /*
    * 读取数据库当中的数据，写入到内存集合当中
    *    支出或者收入的类型
    * */
    public static List<TypeBean> getTypeList(int kind){
        List<TypeBean>list = new ArrayList<>();
//        读取数据库当中的内容
        String sql = "select * from typetb where kind = "+kind;
        Cursor cursor = db.rawQuery(sql, null);
//       循环读取游标内容，然后存储到对象当中
        while (cursor.moveToNext()) {    //查看是否有下一行数据
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            int imageId = cursor.getInt(cursor.getColumnIndex("imageId"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            TypeBean typeBean = new TypeBean(id, typename, imageId, sImageId, kind);
            list.add(typeBean);
        }
        return list;
    }
    /*
    * 向记账的表格当中插入一条元素
    * */
    public static void insertAccountItem(AccountBean bean){
        ContentValues values = new ContentValues();
        values.put("typename",bean.getTypename());
        values.put("sImageId",bean.getsImageId());
        values.put("beizhu",bean.getBeizhu());
        values.put("money",bean.getMoney());
        values.put("time",bean.getTime());
        values.put("year",bean.getYear());
        values.put("month",bean.getMonth());
        values.put("day",bean.getDay());
        values.put("kind",bean.getKind());

        db.insert("accounttb",null,values);
    }
    /*
    * 获取记账表格当中一天所有的收入和支出情况
    * */
    public static List<AccountBean>getAccountListOneDay(int year,int month,int day){
        List<AccountBean>list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=? and day=? order by id desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + ""});
        // 遍历符合要求的每一行数据
        while (cursor.moveToNext()) {
            // 查找这一行当中每一列数据
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            AccountBean bean = new AccountBean(id, typename, sImageId, beizhu, money, time,year ,month, day, kind);
            list.add(bean);
        }
        return list;
    }
    /*
     * 获取记账表格当中一天所有的收入和支出情况
     * */
    public static List<AccountBean>getAccountListOneMonth(int year,int month){
        List<AccountBean>list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=? order by id desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + ""});
        // 遍历符合要求的每一行数据
        while (cursor.moveToNext()) {
            // 查找这一行当中每一列数据
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean bean = new AccountBean(id, typename, sImageId, beizhu, money, time,year ,month, day, kind);
            list.add(bean);
        }
        return list;
    }
    /* 获取某一天的支出或者收入情况总金额   收入-1    支出--0*/
    public static float getSumAccountOneDay(int year,int month,int day,int kind){
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and month=? and day=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", day + "",kind+""});

        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /* 获取某一天的支出或者收入情况有多少条   收入-1    支出--0*/
    public static int getCountAccountOneMonth(int year,int month,int kind){
        int total = 0;
        String sql = "select count(money) from accounttb where year=? and month=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "",kind+""});

        if (cursor.moveToFirst()) {
            int  count = cursor.getInt(cursor.getColumnIndex("count(money)"));
            total = count;
        }
        return total;
    }

    /* 获取某一月的支出或者收入的总金额*/
    public static float getSumAccountOneMonth(int year,int month,int kind){
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and month=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "",kind+""});
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /* 计算一年总支出或者收入金额*/
    public static float getSumAccountOneYear(int year,int kind){
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "",kind+""});
        if (cursor.moveToFirst()) {
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }
        return total;
    }

    /* 根据传入的id，删除accounttb表当中的某一条记录*/
    public static int deleteItemFromAccountById(int id){
        return  db.delete("accounttb", "id=?", new String[]{id + ""});
    }

    /* 查询记账的表当中有几个年份信息*/
    public static List<Integer>getYearListFromAccount(){
        String sql = "select distinct(year) from accounttb order by year asc";
        List<Integer>list = new ArrayList<>();
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            list.add(year);
        }
        return list;
    }

    /* 查询指定年份和月份的收入或者支出每一种类型的总钱数*/
    public static List<ChartItemBean> getChartListFromAccount(int year,int month,int kind){
        float sumAccountOneMonth = getSumAccountOneMonth(year, month, kind);  //求出这个月收入和支出的总钱数
        List<ChartItemBean>list = new ArrayList<>();
        String sql = "select typename,sImageId,sum(money)as total from accounttb where year=? and month=? " +
                "and kind=? group by typename order by total desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        while (cursor.moveToNext()) {
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            float total_money = cursor.getFloat(cursor.getColumnIndex("total"));
            float ratio = FloatUtil.div(total_money,sumAccountOneMonth);
            ChartItemBean bean = new ChartItemBean(sImageId,typename,ratio,total_money);
            list.add(bean);
        }

        return list;
    }

    /* 获取指定月份，每日收入或者支出总钱数的最大金额*/
    public static List<BarChartItemBean> geSumMoneyOneDayInMonth(int year,int month,int kind){
        String sql = "select day,sum(money) from accounttb where year=? and month=? and kind=? " +
                "group by day";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        List<BarChartItemBean>list = new ArrayList<>();
        while (cursor.moveToNext()) {
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            float smoney = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            String moneyStr = "";
            if (smoney>10000) {
                float wan = smoney/10000.0f;
                wan = FloatUtil.saveScale(wan,1);
                moneyStr = "约"+wan+"W";
            }else if (smoney>1000) {
                float qian = smoney/1000.0f;
                qian = FloatUtil.saveScale(qian,1);
                moneyStr = "约"+qian+"K";
            }else {
                float yuan = FloatUtil.saveScale(smoney,1);
                moneyStr = "约"+yuan+"元";
            }
            BarChartItemBean itemBean = new BarChartItemBean(year, month, day, smoney, moneyStr);
            list.add(itemBean);
        }
        return list;
    }

    /* 求出这个月当中那天收入支出的金额最大，值是多少*/
    public static float getMaxMoneyOneDayInMonth(int year,int month,int kind){
        String sql = "select sum(money) from accounttb where year=? and month=? and kind=? " +
                "group by day order by sum(money) desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.getCount()>0) {
            cursor.moveToFirst();
            float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            return money;
        }
        return 0;
    }
    /*
    * 删除accounttb表格当中所有的记录信息
    * */
    public static void deleteAllAccount(){
        String sql = "delete from accounttb";
        db.execSQL(sql);
    }
    /*
    * 根据备注搜索收入或者支出的情况列表
    * */
    public static List<AccountBean>searchInfoByAccount(String text){
        List<AccountBean>list = new ArrayList<>();
        String sql ="select * from accounttb where beizhu like '%"+text+"%'";
        Cursor cursor = db.rawQuery(sql, null);
        // 遍历符合要求的每一行数据
        while (cursor.moveToNext()) {
            // 查找这一行当中每一列数据
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String typename = cursor.getString(cursor.getColumnIndex("typename"));
            int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            String time = cursor.getString(cursor.getColumnIndex("time"));
            float money = cursor.getFloat(cursor.getColumnIndex("money"));
            int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            int day = cursor.getInt(cursor.getColumnIndex("day"));
            int year = cursor.getInt(cursor.getColumnIndex("year"));
            int month = cursor.getInt(cursor.getColumnIndex("month"));

            AccountBean bean = new AccountBean(id, typename, sImageId, beizhu, money, time,year ,month, day, kind);
            list.add(bean);
        }
        return list;
    }
}
